package cn.iocoder.yudao.module.lowcode.service.datasource;

import cn.hutool.core.util.StrUtil;
import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.common.util.object.BeanUtils;
import cn.iocoder.yudao.module.lowcode.controller.admin.datasource.vo.*;
import cn.iocoder.yudao.module.lowcode.dal.dataobject.datasource.LowcodeDataSourceConfigDO;
import cn.iocoder.yudao.module.lowcode.dal.mysql.datasource.LowcodeDataSourceConfigMapper;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.validation.annotation.Validated;

import javax.annotation.PreDestroy;
import javax.annotation.Resource;
import javax.validation.Valid;
import java.sql.*;
import java.util.*;

import static cn.iocoder.yudao.framework.common.exception.util.ServiceExceptionUtil.exception;
import static cn.iocoder.yudao.module.lowcode.enums.ErrorCodeConstants.*;

/**
 * 低代码数据源配置 Service 实现类
 * 
 * 专门为低代码平台设计，支持多种数据库类型的连接管理
 *
 * @author 芋道源码
 */
@Service
@Validated
@Slf4j
public class LowcodeDataSourceConfigServiceImpl implements LowcodeDataSourceConfigService {

    @Resource
    private LowcodeDataSourceConfigMapper lowcodeDataSourceConfigMapper;
    
    @Resource
    private DynamicDataSourceProperties dynamicDataSourceProperties;
    

    @Override
    public Long createDataSourceConfig(@Valid LowcodeDataSourceConfigSaveReqVO createReqVO) {
        // 校验数据源名称唯一性
        validateDataSourceNameUnique(null, createReqVO.getName());
        
        // 测试连接
        LowcodeDataSourceTestReqVO testReqVO = BeanUtils.toBean(createReqVO, LowcodeDataSourceTestReqVO.class);
        LowcodeDataSourceTestRespVO testResult = testConnection(testReqVO);
        if (!testResult.getSuccess()) {
            throw exception(DATASOURCE_CONFIG_TEST_FAILED, testResult.getMessage());
        }

        // 自动推断驱动类名（如果未提供）
        if (StrUtil.isEmpty(createReqVO.getDriverClassName())) {
            createReqVO.setDriverClassName(determineDriverClassName(createReqVO.getUrl()));
        }

        // 插入数据库
        LowcodeDataSourceConfigDO dataSourceConfig = BeanUtils.toBean(createReqVO, LowcodeDataSourceConfigDO.class);
        lowcodeDataSourceConfigMapper.insert(dataSourceConfig);
        
        log.info("[createDataSourceConfig] 创建数据源配置成功: id={}, name={}", dataSourceConfig.getId(), dataSourceConfig.getName());
        return dataSourceConfig.getId();
    }

    @Override
    public void updateDataSourceConfig(@Valid LowcodeDataSourceConfigSaveReqVO updateReqVO) {
        // 校验存在
        validateDataSourceConfigExists(updateReqVO.getId());
        
        // 校验数据源名称唯一性
        validateDataSourceNameUnique(updateReqVO.getId(), updateReqVO.getName());
        
        // 测试连接
        LowcodeDataSourceTestReqVO testReqVO = BeanUtils.toBean(updateReqVO, LowcodeDataSourceTestReqVO.class);
        LowcodeDataSourceTestRespVO testResult = testConnection(testReqVO);
        if (!testResult.getSuccess()) {
            throw exception(DATASOURCE_CONFIG_TEST_FAILED, testResult.getMessage());
        }

        // 自动推断驱动类名（如果未提供）
        if (StrUtil.isEmpty(updateReqVO.getDriverClassName())) {
            updateReqVO.setDriverClassName(determineDriverClassName(updateReqVO.getUrl()));
        }

        // 更新数据库
        LowcodeDataSourceConfigDO updateObj = BeanUtils.toBean(updateReqVO, LowcodeDataSourceConfigDO.class);
        lowcodeDataSourceConfigMapper.updateById(updateObj);
        
        log.info("[updateDataSourceConfig] 更新数据源配置成功: id={}, name={}", updateObj.getId(), updateObj.getName());
    }

    @Override
    public void deleteDataSourceConfig(Long id) {
        // 校验存在
        validateDataSourceConfigExists(id);
        // 删除
        lowcodeDataSourceConfigMapper.deleteById(id);
        
        log.info("[deleteDataSourceConfig] 删除数据源配置成功: id={}", id);
    }

    @Override
    public LowcodeDataSourceConfigDO getDataSourceConfig(Long id) {
        // 处理特殊情况：ID=0时直接使用YAML中的默认数据源配置
        if (id != null && id.equals(0L)) {
            return buildDefaultDataSourceConfig();
        }
        
        return lowcodeDataSourceConfigMapper.selectById(id);
    }

    @Override
    public PageResult<LowcodeDataSourceConfigDO> getDataSourceConfigPage(LowcodeDataSourceConfigPageReqVO pageReqVO) {
        return lowcodeDataSourceConfigMapper.selectPage(pageReqVO);
    }

    @Override
    public List<LowcodeDataSourceConfigDO> getEnabledDataSourceConfigList() {
        List<LowcodeDataSourceConfigDO> result = lowcodeDataSourceConfigMapper.selectEnabledList();
        
        // 确保列表中包含默认数据源
        boolean hasDefault = result.stream().anyMatch(config -> LowcodeDataSourceConfigDO.ID_DEFAULT.equals(config.getId()));
        if (!hasDefault) {
            LowcodeDataSourceConfigDO defaultConfig = getDataSourceConfig(LowcodeDataSourceConfigDO.ID_DEFAULT);
            if (defaultConfig != null) {
                result.add(0, defaultConfig);
            }
        }
        
        return result;
    }

    @Override
    public LowcodeDataSourceConfigDO getDataSourceConfigByName(String name) {
        return lowcodeDataSourceConfigMapper.selectByName(name);
    }

    @Override
    public LowcodeDataSourceTestRespVO testConnection(LowcodeDataSourceTestReqVO testReqVO) {
        LowcodeDataSourceTestRespVO result = new LowcodeDataSourceTestRespVO();
        
        try {
            log.info("[testConnection] 开始测试数据源连接: dbType={}, url={}", testReqVO.getType(), testReqVO.getUrl());
            
            // 确定驱动类名
            String driverClassName = StrUtil.isNotEmpty(testReqVO.getDriverClassName()) 
                ? testReqVO.getDriverClassName() 
                : determineDriverClassName(testReqVO.getUrl());

            // 创建连接
            Class.forName(driverClassName);
            try (Connection connection = DriverManager.getConnection(testReqVO.getUrl(), testReqVO.getUsername(), testReqVO.getPassword())) {
                
                DatabaseMetaData metaData = connection.getMetaData();
                
                result.setSuccess(true);
                result.setMessage("连接成功");
                result.setDatabaseProductName(metaData.getDatabaseProductName());
                result.setDatabaseProductVersion(metaData.getDatabaseProductVersion());
                result.setDriverName(metaData.getDriverName());
                result.setDriverVersion(metaData.getDriverVersion());
                result.setUrl(metaData.getURL());
                result.setUsername(metaData.getUserName());
                
                // 获取表信息
                List<String> sampleTables = getSampleTableList(connection, testReqVO.getType(), null);
                result.setTableCount(sampleTables.size());
                result.setSampleTables(sampleTables.size() > 5 ? sampleTables.subList(0, 5) : sampleTables);
                
                log.info("[testConnection] 数据源连接测试成功: {} - {}, 发现 {} 个表", 
                        result.getDatabaseProductName(), result.getDatabaseProductVersion(), sampleTables.size());
                
            }
        } catch (Exception e) {
            log.error("[testConnection] 数据源连接测试失败: {}", testReqVO.getUrl(), e);
            result.setSuccess(false);
            result.setMessage("连接失败: " + getDetailedErrorMessage(e));
        }
        
        return result;
    }

    @Override
    public List<String> getTableList(Long id, String tableName) {
        LowcodeDataSourceConfigDO config = getDataSourceConfig(id);
        if (config == null) {
            return new ArrayList<>();
        }
        
        List<String> tables = new ArrayList<>();
        
        try {
            Class.forName(config.getDriverClassName());
            try (Connection connection = DriverManager.getConnection(config.getUrl(), config.getUsername(), config.getPassword())) {
                
                DatabaseMetaData metaData = connection.getMetaData();
                String catalog = getCatalogName(connection, config.getType());
                String schema = getSchemaName(connection, config.getType(), null);
                String tablePattern = StrUtil.isNotEmpty(tableName) ? "%" + tableName + "%" : "%";
                
                try (ResultSet rs = metaData.getTables(catalog, schema, tablePattern, new String[]{"TABLE"})) {
                    while (rs.next()) {
                        String table = rs.getString("TABLE_NAME");
                        // 过滤系统表
                        if (!isSystemTable(table, config.getType())) {
                            tables.add(table);
                        }
                    }
                }
                
                log.debug("[getTableList] 获取到 {} 个表: dataSourceId={}", tables.size(), id);
                
            }
        } catch (Exception e) {
            log.error("[getTableList] 获取表列表失败: dataSourceId={}", id, e);
        }
        
        return tables;
    }

    @Override
    public List<LowcodeTableInfoRespVO> getTableInfoList(Long id, String tableName) {
        LowcodeDataSourceConfigDO config = getDataSourceConfig(id);
        if (config == null) {
            return new ArrayList<>();
        }
        
        List<LowcodeTableInfoRespVO> tables = new ArrayList<>();
        
        try {
            Class.forName(config.getDriverClassName());
            try (Connection connection = DriverManager.getConnection(config.getUrl(), config.getUsername(), config.getPassword())) {
                
                DatabaseMetaData metaData = connection.getMetaData();
                String catalog = getCatalogName(connection, config.getType());
                String schema = getSchemaName(connection, config.getType(), null);
                String tablePattern = StrUtil.isNotEmpty(tableName) ? "%" + tableName + "%" : "%";
                
                try (ResultSet rs = metaData.getTables(catalog, schema, tablePattern, new String[]{"TABLE"})) {
                    while (rs.next()) {
                        String table = rs.getString("TABLE_NAME");
                        String comment = rs.getString("REMARKS");
                        
                        // 过滤系统表
                        if (!isSystemTable(table, config.getType())) {
                            // 如果通过标准方式获取不到注释，尝试数据库特定的方式
                            if (StrUtil.isEmpty(comment)) {
                                comment = getTableCommentByDbType(connection, config.getType(), catalog, schema, table);
                            }
                            
                            LowcodeTableInfoRespVO tableInfo = new LowcodeTableInfoRespVO();
                            tableInfo.setName(table);
                            tableInfo.setComment(StrUtil.isNotEmpty(comment) ? comment : "");
                            tables.add(tableInfo);
                        }
                    }
                }
                
                log.debug("[getTableInfoList] 获取到 {} 个表: dataSourceId={}", tables.size(), id);
                
            }
        } catch (Exception e) {
            log.error("[getTableInfoList] 获取表信息列表失败: dataSourceId={}", id, e);
        }
        
        return tables;
    }

    @Override
    public List<TableColumnInfo> getTableColumnList(Long id, String tableName) {
        LowcodeDataSourceConfigDO config = getDataSourceConfig(id);
        if (config == null) {
            return new ArrayList<>();
        }
        
        List<TableColumnInfo> columns = new ArrayList<>();
        
        try {
            Class.forName(config.getDriverClassName());
            try (Connection connection = DriverManager.getConnection(config.getUrl(), config.getUsername(), config.getPassword())) {
                
                DatabaseMetaData metaData = connection.getMetaData();
                String catalog = getCatalogName(connection, config.getType());
                String schema = getSchemaName(connection, config.getType(), null);
                
                // 获取主键信息
                Map<String, Boolean> primaryKeys = new HashMap<>();
                try (ResultSet pkRs = metaData.getPrimaryKeys(catalog, schema, tableName)) {
                    while (pkRs.next()) {
                        primaryKeys.put(pkRs.getString("COLUMN_NAME"), true);
                    }
                }
                
                // 获取列信息
                try (ResultSet rs = metaData.getColumns(catalog, schema, tableName, "%")) {
                    while (rs.next()) {
                        TableColumnInfo column = new TableColumnInfo();
                        column.setColumnName(rs.getString("COLUMN_NAME"));
                        column.setDataType(rs.getString("TYPE_NAME"));
                        column.setNullable("YES".equals(rs.getString("IS_NULLABLE")));
                        column.setColumnComment(rs.getString("REMARKS"));
                        column.setPrimaryKey(primaryKeys.getOrDefault(column.getColumnName(), false));
                        columns.add(column);
                    }
                }
                
                log.debug("[getTableColumnList] 获取到 {} 个字段: dataSourceId={}, tableName={}", columns.size(), id, tableName);
                
            }
        } catch (Exception e) {
            log.error("[getTableColumnList] 获取表字段失败: dataSourceId={}, tableName={}", id, tableName, e);
        }
        
        return columns;
    }

    @Override
    public void initDefaultDataSourceConfig() {
        // TODO: 如果需要初始化默认数据源配置，可以在这里实现
    }

    @Override
    public List<String> getSupportedDatabaseTypes() {
        return Arrays.asList(
            "MySQL",
            "PostgreSQL", 
            "Oracle",
            "SQLServer",
            "DM",
            "KingBase",
            "OpenGauss",
            "H2",
            "SQLite"
        );
    }


    /**
     * 应用关闭时清理资源
     */
    @PreDestroy
    public void destroy() {
        log.info("[destroy] 低代码数据源服务正在关闭，清理资源...");
        // 这里可以添加额外的清理逻辑，如果有必要的话
        log.info("[destroy] 低代码数据源服务资源清理完成");
    }

    // 私有辅助方法

    private void validateDataSourceConfigExists(Long id) {
        if (lowcodeDataSourceConfigMapper.selectById(id) == null) {
            throw exception(DATASOURCE_CONFIG_NOT_EXISTS);
        }
    }

    private void validateDataSourceNameUnique(Long id, String name) {
        LowcodeDataSourceConfigDO config = lowcodeDataSourceConfigMapper.selectByName(name);
        if (config != null && (id == null || !id.equals(config.getId()))) {
            throw exception(DATASOURCE_CONFIG_NAME_DUPLICATE);
        }
    }

    /**
     * 构建默认数据源配置
     */
    private LowcodeDataSourceConfigDO buildDefaultDataSourceConfig() {
        String primary = dynamicDataSourceProperties.getPrimary();
        com.baomidou.dynamic.datasource.creator.DataSourceProperty dataSourceProperty = 
            dynamicDataSourceProperties.getDatasource().get(primary);
        
        if (dataSourceProperty == null) {
            throw new RuntimeException("无法获取主数据源配置");
        }
        
        LowcodeDataSourceConfigDO config = new LowcodeDataSourceConfigDO();
        config.setId(0L); // 使用ID=0表示这是YAML中的默认数据源
        config.setName("默认数据源(YAML)");
        config.setType(determineDbType(dataSourceProperty.getUrl()));
        config.setUrl(dataSourceProperty.getUrl());
        config.setUsername(dataSourceProperty.getUsername());
        config.setPassword(dataSourceProperty.getPassword());
        config.setDriverClassName(determineDriverClassName(dataSourceProperty.getUrl()));
        config.setInitialSize(1);
        config.setMinIdle(1);
        config.setMaxActive(8);
        config.setTestQuery("SELECT 1");
        config.setStatus(0); // 状态：0启用，1禁用
        
        return config;
    }

    /**
     * 根据URL确定数据库类型
     */
    private String determineDbType(String url) {
        if (url == null) return "MySQL";
        
        String lowerUrl = url.toLowerCase();
        if (lowerUrl.contains("mysql")) return "MySQL";
        if (lowerUrl.contains("postgresql")) return "PostgreSQL";
        if (lowerUrl.contains("oracle")) return "Oracle";
        if (lowerUrl.contains("sqlserver")) return "SQLServer";
        if (lowerUrl.contains("dm")) return "DM";
        if (lowerUrl.contains("kingbase")) return "KingBase";
        if (lowerUrl.contains("opengauss")) return "OpenGauss";
        if (lowerUrl.contains("h2")) return "H2";
        if (lowerUrl.contains("sqlite")) return "SQLite";
        
        return "MySQL"; // 默认
    }

    /**
     * 根据URL确定驱动类名
     */
    private String determineDriverClassName(String url) {
        if (url == null) return "com.mysql.cj.jdbc.Driver";
        
        String lowerUrl = url.toLowerCase();
        if (lowerUrl.contains("mysql")) return "com.mysql.cj.jdbc.Driver";
        if (lowerUrl.contains("postgresql")) return "org.postgresql.Driver";
        if (lowerUrl.contains("oracle")) return "oracle.jdbc.OracleDriver";
        if (lowerUrl.contains("sqlserver")) return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        if (lowerUrl.contains("dm")) return "dm.jdbc.driver.DmDriver";
        if (lowerUrl.contains("kingbase")) return "com.kingbase8.Driver";
        if (lowerUrl.contains("opengauss")) return "org.opengauss.Driver";
        if (lowerUrl.contains("h2")) return "org.h2.Driver";
        if (lowerUrl.contains("sqlite")) return "org.sqlite.JDBC";
        
        return "com.mysql.cj.jdbc.Driver"; // 默认
    }

    /**
     * 获取示例表列表
     */
    private List<String> getSampleTableList(Connection connection, String dbType, String schema) throws SQLException {
        List<String> tables = new ArrayList<>();
        
        DatabaseMetaData metaData = connection.getMetaData();
        String catalog = getCatalogName(connection, dbType);
        String schemaName = getSchemaName(connection, dbType, schema);
        
        try (ResultSet rs = metaData.getTables(catalog, schemaName, "%", new String[]{"TABLE"})) {
            int count = 0;
            while (rs.next() && count < 10) { // 最多获取10个示例表
                String tableName = rs.getString("TABLE_NAME");
                if (!isSystemTable(tableName, dbType)) {
                    tables.add(tableName);
                    count++;
                }
            }
        }
        
        return tables;
    }

    /**
     * 获取目录名
     */
    private String getCatalogName(Connection connection, String dbType) throws SQLException {
        if ("MySQL".equalsIgnoreCase(dbType) || "SQLServer".equalsIgnoreCase(dbType)) {
            return connection.getCatalog();
        }
        return null;
    }

    /**
     * 获取模式名
     */
    private String getSchemaName(Connection connection, String dbType, String configSchema) throws SQLException {
        if (StrUtil.isNotEmpty(configSchema)) {
            return configSchema;
        }
        
        if ("MySQL".equalsIgnoreCase(dbType)) {
            return null;
        } else if ("PostgreSQL".equalsIgnoreCase(dbType) || "OpenGauss".equalsIgnoreCase(dbType) || "KingBase".equalsIgnoreCase(dbType)) {
            return connection.getSchema() != null ? connection.getSchema() : "public";
        } else if ("Oracle".equalsIgnoreCase(dbType) || "DM".equalsIgnoreCase(dbType)) {
            return connection.getMetaData().getUserName();
        } else if ("SQLServer".equalsIgnoreCase(dbType)) {
            return "dbo";
        }
        
        return null;
    }

    /**
     * 判断是否为系统表
     */
    private boolean isSystemTable(String tableName, String dbType) {
        if (tableName == null) return true;
        
        String lowerTableName = tableName.toLowerCase();
        
        // 通用系统表模式
        if (lowerTableName.startsWith("act_") || lowerTableName.startsWith("qrtz_") || 
            lowerTableName.startsWith("flw_") || lowerTableName.startsWith("tmp_") ||
            lowerTableName.startsWith("temp_") || lowerTableName.startsWith("bak_")) {
            return true;
        }
        
        // 根据数据库类型判断
        if ("MySQL".equalsIgnoreCase(dbType)) {
            return lowerTableName.startsWith("information_schema") || 
                   lowerTableName.startsWith("performance_schema") ||
                   lowerTableName.startsWith("mysql") || lowerTableName.startsWith("sys");
        } else if ("Oracle".equalsIgnoreCase(dbType)) {
            return lowerTableName.startsWith("all_") || lowerTableName.startsWith("user_") ||
                   lowerTableName.startsWith("dba_") || lowerTableName.startsWith("v$") ||
                   lowerTableName.startsWith("sys_") || lowerTableName.contains("$");
        } else if ("PostgreSQL".equalsIgnoreCase(dbType) || "OpenGauss".equalsIgnoreCase(dbType)) {
            return lowerTableName.startsWith("pg_") || lowerTableName.startsWith("information_schema");
        } else if ("SQLServer".equalsIgnoreCase(dbType)) {
            return lowerTableName.startsWith("sys") || lowerTableName.startsWith("msdb") ||
                   lowerTableName.startsWith("__") || lowerTableName.startsWith("ms");
        }
        
        return false;
    }

    /**
     * 获取详细错误信息
     */
    private String getDetailedErrorMessage(Exception e) {
        StringBuilder sb = new StringBuilder(e.getMessage() != null ? e.getMessage() : "未知错误");
        
        if (e.getCause() != null) {
            sb.append(" (原因: ").append(e.getCause().getMessage()).append(")");
        }
        
        return sb.toString();
    }

    /**
     * 根据数据库类型获取表注释
     */
    private String getTableCommentByDbType(Connection connection, String dbType, String catalog, String schema, String tableName) {
        try {
            String sql = null;
            
            if ("MySQL".equalsIgnoreCase(dbType)) {
                sql = "SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
                try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                    pstmt.setString(1, catalog != null ? catalog : connection.getCatalog());
                    pstmt.setString(2, tableName);
                    try (ResultSet rs = pstmt.executeQuery()) {
                        if (rs.next()) {
                            return rs.getString("TABLE_COMMENT");
                        }
                    }
                }
            } else if ("PostgreSQL".equalsIgnoreCase(dbType) || "OpenGauss".equalsIgnoreCase(dbType)) {
                sql = "SELECT description FROM pg_description pd JOIN pg_class pc ON pd.objoid = pc.oid WHERE pc.relname = ? AND pd.objsubid = 0";
                try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                    pstmt.setString(1, tableName);
                    try (ResultSet rs = pstmt.executeQuery()) {
                        if (rs.next()) {
                            return rs.getString("description");
                        }
                    }
                }
            } else if ("Oracle".equalsIgnoreCase(dbType)) {
                sql = "SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME = UPPER(?)";
                try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                    pstmt.setString(1, tableName);
                    try (ResultSet rs = pstmt.executeQuery()) {
                        if (rs.next()) {
                            return rs.getString("COMMENTS");
                        }
                    }
                }
            } else if ("SQLServer".equalsIgnoreCase(dbType)) {
                sql = "SELECT ep.value FROM sys.tables t LEFT JOIN sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = 0 AND ep.name = 'MS_Description' WHERE t.name = ?";
                try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                    pstmt.setString(1, tableName);
                    try (ResultSet rs = pstmt.executeQuery()) {
                        if (rs.next()) {
                            return rs.getString("value");
                        }
                    }
                }
            }
            
        } catch (Exception e) {
            log.debug("[getTableCommentByDbType] 获取表注释失败: dbType={}, tableName={}", dbType, tableName, e);
        }
        
        return null;
    }


}